/*	http://code.google.com/p/wamon/
 *	Windows Activity Monitor - Monitors user's activity on the computer, 
 *	tracks active windows and processes, allows table and chart visualization.
 *	Copyright (C) 2009  Archae s.r.o. (http://archaedev.com/)
 *
 *	This program is free software: you can redistribute it and/or modify
 *	it under the terms of the GNU General Public License as published by
 *	the Free Software Foundation, either version 3 of the License, or
 *	(at your option) any later version.
 *
 *	This program is distributed in the hope that it will be useful,
 *	but WITHOUT ANY WARRANTY; without even the implied warranty of
 *	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *	GNU General Public License for more details.
 *
 *	You should have received a copy of the GNU General Public License
 *	along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
using System;
using System.Collections.Generic;
using System.Data.SQLite;

namespace Archae.Wamon
{
	struct Group
	{
		public Group(int id, string name, string windowRegular, string processRegular)
		{
			this.id = id;
			this.name = name;
			this.windowRegular = windowRegular;
			this.processRegular = processRegular;
		}
		public int id;
		public string name;
		public string windowRegular;
		public string processRegular;
	}

	[SQLiteFunction(Name = "REGEXP", Arguments = 2, FuncType = FunctionType.Scalar)]
	class MyRegEx : SQLiteFunction
	{
		public override object Invoke(object[] args)
		{
			return System.Text.RegularExpressions.Regex.IsMatch(Convert.ToString(args[1]).ToLower(), Convert.ToString(args[0]).ToLower());
		}
	}

	class DB
	{
		private SQLiteConnection connection = null;
		private static DB singleton = null;
		private DB()
		{
			string dbPath = Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData) + "\\wam\\wam.dat";
			// Open the DB connection
			connection = new SQLiteConnection("Data Source=" + dbPath);
			connection.Open();
			// Create tables
			new SQLiteCommand("CREATE TABLE IF NOT EXISTS snapshot (id INTEGER PRIMARY KEY, time TEXT, window TEXT, process TEXT, user TEXT)", connection).ExecuteNonQuery();
			new SQLiteCommand("CREATE TABLE IF NOT EXISTS settings (id INTEGER PRIMARY KEY, key TEXT, value TEXT)", connection).ExecuteNonQuery();
			new SQLiteCommand("CREATE TABLE IF NOT EXISTS `group` (id INTEGER PRIMARY KEY, name TEXT, window TEXT, process TEXT)", connection).ExecuteNonQuery();
			new SQLiteCommand("CREATE TABLE IF NOT EXISTS process (id INTEGER PRIMARY KEY, name TEXT)", connection).ExecuteNonQuery();
			new SQLiteCommand("CREATE TABLE IF NOT EXISTS process_snapshot (id INTEGER PRIMARY KEY, process_id INTEGER, time TEXT, user TEXT)", connection).ExecuteNonQuery();
			new SQLiteCommand("CREATE TABLE IF NOT EXISTS error_log (id INTEGER PRIMARY KEY, created INTEGER, error TEXT)", connection).ExecuteNonQuery();
			// Remove old entries from history
			DateTime history = DateTime.Now.AddDays(-HistoryDays);
			new SQLiteCommand("DELETE FROM snapshot WHERE STRFTIME('%s', DATE(time)) < STRFTIME('%s', DATE(" + q(history.ToString("u")) + "))", connection).ExecuteNonQuery();
			new SQLiteCommand("DELETE FROM process_snapshot WHERE STRFTIME('%s', DATE(time)) < STRFTIME('%s', DATE(" + q(history.ToString("u")) + "))", connection).ExecuteNonQuery();
		}
		internal void Close()
		{
			connection.Close();
			singleton = null;
		}
		internal static DB Singleton
		{
			get
			{
				if (singleton == null)
					singleton = new DB();
				return singleton;
			}
		}
		private string q(string str)
		{
			return "'" + str.Replace("'", "''") + "'";
		}

		internal string GetCharts(DateTime from, DateTime to, string user)
		{
			try
			{
				if (user != null && user != "all_users" && user != "")
					user = " AND user='" + user + "'";
				else
					user = "";
				string str = "http://chart.apis.google.com/chart?cht=p&chd=t:";
				SQLiteDataReader reader = new SQLiteCommand(
					"SELECT process, IFNULL(COUNT(process), 0) AS count_process FROM  snapshot WHERE STRFTIME('%s', DATE(time))>=STRFTIME('%s', DATE('" +
					from.ToString("u") + "')) AND STRFTIME('%s', DATE(time))<=STRFTIME('%s', DATE('" + to.ToString("u") + "'))" + user + " GROUP BY process"
					, connection).ExecuteReader();

				if (!reader.HasRows)
				{
					reader.Close();
					return string.Empty;
				}
				long count = (long)new SQLiteCommand("SELECT IFNULL(COUNT(process), 0) FROM snapshot WHERE STRFTIME('%s', DATE(time))>=STRFTIME('%s', DATE('" +
						from.ToString("u") + "')) AND STRFTIME('%s', DATE(time))<=STRFTIME('%s', DATE('" + to.ToString("u") + "'))" + user, connection).ExecuteScalar();

				double other = 0.0;
				string per = "", name = "";
				while (reader.Read())
				{
					double temp = (((long)reader["count_process"]) / (count / 100.0));
					if (temp < 1.0)
					{
						other += temp;
						continue;
					}
					per += "," + ((int)Math.Round(temp, 0)).ToString();
					string process = (string)reader["process"];
					string fileDesc = (process == "IDLE" ? Dict.Singleton["idle"] : GetFileDescription(process));
					if (fileDesc == null)
					{
						fileDesc = process;
						if (fileDesc.Length > 30)
							fileDesc = "..." + fileDesc.Substring(fileDesc.Length - 27);
					}
					else if (fileDesc.Length > 30)
						fileDesc = fileDesc.Substring(0, 27) + "...";
					name += "|" + System.Web.HttpUtility.UrlEncode(fileDesc);
				}
				if (other > 1.0)
				{
					per += "," + ((int)Math.Round(other, 0)).ToString();
					name += "|" + System.Web.HttpUtility.UrlEncode(Dict.Singleton["other"]);
				}
				reader.Close();
				return str + per.Substring(1) + "&chs=700x300&chl=" + name.Substring(1);
			}
			catch { return string.Empty; }
		}
		internal string GetGroupsCharts(DateTime from, DateTime to, string user)
		{
			if (user != null && user != "all_users" && user != "")
				user = " AND user='" + user + "'";
			else
				user = "";
			string str = "http://chart.apis.google.com/chart?cht=p&chd=t:";
			
			SQLiteDataReader reader = new SQLiteCommand(
				"SELECT group_name, IFNULL(COUNT(process), 0) AS count_process FROM " +
				"(SELECT *, (SELECT `group`.name FROM `group` WHERE " +
				"(`group`.process!='' AND snapshot.process REGEXP `group`.process) OR " + 
				"(`group`.window!='' AND snapshot.window REGEXP `group`.window) ORDER BY `group`.id LIMIT 1) " +
				"AS group_name FROM snapshot ) WHERE group_name NOT NULL AND " + 
				"STRFTIME('%s', DATE(time))>=STRFTIME('%s', DATE('" + from.ToString("u") + 
				"')) AND STRFTIME('%s', DATE(time))<=STRFTIME('%s', DATE('" + to.ToString("u") + "'))" + 
				user + "GROUP BY group_name"
				, connection).ExecuteReader();

			if (!reader.HasRows)
			{
				reader.Close();
				return string.Empty;
			}
			long count = (long)new SQLiteCommand("SELECT IFNULL(COUNT(process), 0) " + 
				"FROM snapshot WHERE STRFTIME('%s', DATE(time))>=STRFTIME('%s', DATE('" +
				from.ToString("u") + "')) AND STRFTIME('%s', DATE(time))<=STRFTIME('%s', DATE('" 
				+ to.ToString("u") + "'))" + user, connection).ExecuteScalar();

			string per = "", name = "";

			int groupsTile = 0;
			while (reader.Read())
			{
				int temp = (int)(((long)reader["count_process"]) / (count / 100.0));
				if (temp == 0)
					continue;
				groupsTile += temp;
				per += "," + temp.ToString();
				name += "|" + System.Web.HttpUtility.UrlEncode((string)reader["group_name"]);
			}
			if (groupsTile < 100)
			{
				groupsTile = (100 - groupsTile);
				per += "," + groupsTile.ToString();
				name += "|" + System.Web.HttpUtility.UrlEncode(Dict.Singleton["other"]);
			}
			reader.Close();
			return str + per.Substring(1) + "&chs=700x300&chl=" + name.Substring(1);
		}
		internal string [] GetLastActivity(string user)
		{
			if (user != null && user != "all_users" && user != "")
				user = " WHERE user='" + user + "'";
			else
				user = "";
			SQLiteDataReader reader = new SQLiteCommand(
				"SELECT process, window FROM  snapshot" + user + " ORDER BY id DESC LIMIT 1", connection).ExecuteReader();
			if (!reader.Read() || (string)reader["process"] == null || (string)reader["process"] == string.Empty
				|| (string)reader["window"] == null || (string)reader["window"] == string.Empty)
			{
				reader.Close();
				return null;
			}

			string window = (string)reader["window"];
			string fileDesc = GetFileDescription((string)reader["process"]);
			if (fileDesc == null)
			{
				string [] ret = { window, (string)reader["process"] };
				reader.Close();
				return ret;
			}
			reader.Close();
			string[] retStr = { window, fileDesc };
			return retStr;
		}
		internal string [] GetLastActivityGroup(string user)
		{
			if (user != null && user != "all_users" && user != "")
				user = " AND user='" + user + "'";
			else
				user = "";
			SQLiteDataReader reader = new SQLiteCommand(
				"SELECT group_name, process, id, time FROM " +
				"(SELECT *, (SELECT `group`.name FROM `group` WHERE " +
				"(`group`.process!='' AND snapshot.process REGEXP `group`.process) OR " + 
				"(`group`.window!='' AND snapshot.window REGEXP `group`.window) ORDER BY `group`.id LIMIT 1) " +
				"AS group_name FROM snapshot ) WHERE group_name NOT NULL " + 
				user + "ORDER BY id DESC LIMIT 1"
				, connection).ExecuteReader();
			if (!reader.Read())
				return null;
			if ((string)reader["group_name"] == null || (string)reader["group_name"] == string.Empty
				|| (string)reader["process"] == null || (string)reader["process"] == string.Empty
				|| (string)reader["time"] == null || (string)reader["time"] == string.Empty)
			{
				reader.Close();
				return null;
			}
			string strTime = "";
			try { strTime = DateTime.Parse((string)reader["time"]).ToString(Dict.Singleton.CultureInfo); }
			catch { strTime = ""; }
			string[] ret = { (string)reader["group_name"], strTime };
			reader.Close();
			return ret;
		}
		internal string GetFileDescription(string file)
		{
			try
			{
				System.Diagnostics.FileVersionInfo versionInfo = System.Diagnostics.FileVersionInfo.GetVersionInfo(file);
				return (versionInfo.FileDescription == string.Empty) ? null : versionInfo.FileDescription;
			}
			catch { return null; }
		}
		internal SQLiteDataReader GetUsers()
		{
			return new SQLiteCommand("SELECT user FROM snapshot WHERE user<>'SYSTEM' GROUP BY user", connection).ExecuteReader();
		}
		private void SetSettings(string key, string value)
		{
			if (new SQLiteCommand("SELECT value FROM settings WHERE key=" + q(key), connection).ExecuteScalar() == null)
				new SQLiteCommand("INSERT INTO settings (key, value) VALUES (" + q(key) + ", " + q(value) + ")", connection).ExecuteNonQuery();
			else
				new SQLiteCommand("UPDATE settings SET value=" + q(value) + " WHERE key=" + q(key), connection).ExecuteNonQuery();
		}
		private object GetSettings(string key)
		{
			try { return new SQLiteCommand("SELECT value FROM settings WHERE key=" + q(key), connection).ExecuteScalar(); }
			catch { return null; }
		}
		internal void AddGroup(Group group)
		{
			new SQLiteCommand("INSERT INTO `group` (name, window, process) VALUES(" + q(group.name) + ", " + 
				q(group.windowRegular == null ? "" : group.windowRegular) + ", " + 
				q(group.processRegular == null ? "" : group.processRegular) + ")", connection).ExecuteNonQuery();
		}
		internal Group GetGroup(string name)
		{
			SQLiteDataReader reader = new SQLiteCommand("SELECT id, name, window, process FROM `group` WHERE name=" + q(name), connection).ExecuteReader();
			Group group = new Group(0, null, null, null);
			if (reader.Read())
			{
				group.id = int.Parse(reader["id"].ToString());
				group.name = (string)reader["name"];
				group.windowRegular = (string)reader["window"] == string.Empty ? null : (string)reader["window"];
				group.processRegular = (string)reader["process"] == string.Empty ? null : (string)reader["process"];
			}
			reader.Close();
			return group;
		}
		internal List<Group> GetGroups()
		{
			List<Group> groups = new List<Group>();
			SQLiteDataReader reader = new SQLiteCommand("SELECT id, name, window, process FROM `group` ORDER BY id", connection).ExecuteReader();
			while (reader.Read())
			{
				groups.Add(new Group(int.Parse(reader["id"].ToString()) ,(string)reader["name"], 
					(string)reader["window"] == string.Empty ? null : (string)reader["window"], 
					(string)reader["process"] == string.Empty ? null : (string)reader["process"]));
			}
			reader.Close();
			return groups;
		}
		internal void RemoveGroup(int id)
		{
			new SQLiteCommand("DELETE FROM `group` WHERE id=" + id, connection).ExecuteNonQuery();
		}
		internal int Port
		{
			get
			{
				try { return int.Parse((string)GetSettings("Port")); }
				catch { return 57824; }
			}
			set { SetSettings("Port", value.ToString()); }
		}
		internal int HistoryDays
		{
			get
			{
				try { return int.Parse((string)GetSettings("history")); }
				catch { return 30; }
			}
			set { SetSettings("history", value.ToString()); }
		}
		internal bool RemoteAccess
		{
			get
			{
				try { return (string)GetSettings("remote_access") == "1"; }
				catch { return false; }
			}
			set { SetSettings("remote_access", value ? "1" : "0"); }
		}
		internal bool RecordIdle
		{
			get
			{
				try { return (string)GetSettings("RecordIdle") == "1"; }
				catch { return false; }
			}
			set { SetSettings("RecordIdle", value ? "1" : "0"); }
		}
		internal string Language
		{
			get { return (string)GetSettings("language"); }
			set { SetSettings("language", value); }
		}
		internal string Password
		{
			get { return (string)GetSettings("password"); }
			set { SetSettings("password", value); }
		}
		internal void DeletePassword()
		{
			new SQLiteCommand("DELETE FROM settings WHERE key='password'", connection).ExecuteNonQuery();
		}
		internal SQLiteDataReader GetTimeSheet(DateTime time, string user)
		{
			if (user == null || user == string.Empty || user == "all_users")
				user = string.Empty;
			else
				user = " AND user=" + q(user);
			return new SQLiteCommand("SELECT * FROM snapshot WHERE DATE(time)=DATE(" + 
				q(time.ToString("u").Split(' ')[0]) + ")" + user + " ORDER BY id", connection).ExecuteReader();
		}
		internal SQLiteDataReader Search(string search)
		{
			search = "%" + search + "%";
			return new SQLiteCommand("SELECT * FROM snapshot WHERE process LIKE " + q(search) +
				 " OR window LIKE " + q(search) + " ORDER BY id DESC LIMIT 1000", connection).ExecuteReader();
		}
		internal void AddProcess(string process)
		{
			new SQLiteCommand("INSERT INTO process(name) VALUES(" + q(process) + ")", connection).ExecuteNonQuery();
		}
		internal SQLiteDataReader GetPocesses()
		{
			return new SQLiteCommand("SELECT * FROM process", connection).ExecuteReader();
		}
		internal void RemoveProcess(int id)
		{
			new SQLiteCommand("DELETE FROM process WHERE id=" + id, connection).ExecuteNonQuery();
		}
		internal uint GetProcessID(string processName)
		{
			object obj = new SQLiteCommand("SELECT id FROM process WHERE LOWER(name)=LOWER(" + q(processName) + ")", connection).ExecuteScalar();
			return (obj == null ? 0 : Convert.ToUInt32(obj));
		}
		internal void InsertProcessSnapshot(uint processId, string user)
		{
			new SQLiteCommand("INSERT INTO process_snapshot (time, user, process_id) VALUES (strftime('%Y-%m-%d %H:%M:%S', 'now', 'localtime'), " + q(user) + ", " + processId + ")", connection).ExecuteNonQuery();
		}

		internal string GetProcessSnapshots(DateTime from, DateTime to, string user)
		{
			if (user == null || user == "" || user == "all_users")
				user = string.Empty;
			else
				user = " AND user=" + q(user);

			SQLiteDataReader reader = new SQLiteCommand("SELECT id, name FROM process", connection).ExecuteReader();

			long max_count = (long)new SQLiteCommand("SELECT IFNULL(COUNT(id), 0) FROM snapshot WHERE " +
				"STRFTIME('%s', DATE(time))>=STRFTIME('%s', DATE('" + from.ToString("u") +
				"')) AND STRFTIME('%s', DATE(time))<=STRFTIME('%s', DATE('" + to.ToString("u") + "'))"
				, connection).ExecuteScalar();
			if (!reader.HasRows)
			{
				reader.Close();
				return string.Empty;
			}
			string html = "<br/><br/><table class=\"border\" border=\"1\" style=\"width:100%; line-height: 130%;\">";
			while (reader.Read())
			{
				long count = (long)new SQLiteCommand("SELECT IFNULL(COUNT(id), 0) AS count " +
				"FROM process_snapshot WHERE process_id=" + reader["id"] + 
				" AND STRFTIME('%s', DATE(time))>=STRFTIME('%s', DATE('" + from.ToString("u") +
				"')) AND STRFTIME('%s', DATE(time))<=STRFTIME('%s', DATE('" + to.ToString("u") + "'))" +
				user, connection).ExecuteScalar();

				double per = 0.0;
				try { per = count / (max_count / 100.0); }
				catch { per = 0.0; ; }
				if (count == 0)
					per = 0.0;
				if (per > 100.0)
					per = 100.0;
				html += "<tr><td nowrap=\"nowrap\"><div style=\"position:relative; background-color: #fff; width:100%; height:20px;\">" +
					"<div style=\"position:absolute; top:0; background-color: #bedabd; max-width: 100%; width:" + ((int)Math.Round(per)).ToString() + "%;\">&nbsp;" + 
					"<div style=\"position:absolute; top:0; left:15px; font-size:120%;\"><span id=\"proc" + ((long)reader["id"]).ToString() + "\">" + (string)reader["name"] + "</span><span>&nbsp;&nbsp;&nbsp;</span>" +
					"<span style=\"font-size:80%; font-style: italic;\">" + per.ToString("F") + "%</span></div></div></div></td>" +
					"<td style=\"width:100px;\"><a href=\"remove_process?id=" + ((long)reader["id"]).ToString() + "\" " + 
					"onclick=\"if (!confirm('" + Dict.Singleton["remove_process_confirm"] + " ' + $('proc" + ((long)reader["id"]).ToString() + "').innerHTML + '?')) return false;\">"
					+ Dict.Singleton["remove"] + "</a></td></tr>";
			}
			html += "</table>";
			reader.Close();
			return html;
		}

		internal void InsertSnapshot(string window, string process, string user)
		{
			new SQLiteCommand("INSERT INTO snapshot (time, window, process, user) VALUES (strftime('%Y-%m-%d %H:%M:%S', 'now', 'localtime'), " +
				q(window) + ", " + q(process) + ", " + q(user) + ")", connection).ExecuteNonQuery();
		}

		internal void Log(String error)
		{
			new SQLiteCommand("INSERT INTO error_log (created, error) VALUES (STRFTIME('%s', 'now'), " + q(error) + ")", connection).ExecuteNonQuery();
		}
	}
}
